package dao;

import beans.UsuarioTo;
import java.sql.Date;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.HashMap;

import utildb.GenericDAO;
import utildb.Transaction;
import utildb.TransactionException;

public class UsuarioDAO extends GenericDAO  {
	static HashMap usuarios = null;
	
	public UsuarioDAO(Transaction transacction) {
		super(transacction);
	}
	
	public void create(UsuarioTo to) throws TransactionException {
		try {
        		int i = 1;
                        UsuarioTo busUser = buscarUsuario(to.getCcod_Usua() );
                        if (busUser == null) {
                            Connection c;
                            PreparedStatement statement = null;
                            c = (Connection) transaction.getConnection();

                            statement = c.prepareStatement("insert into mysql.gemc_usua "
                                            + " (ccod_Usua, cnom_Usua, cnic_Usua, "
                                             + " ccla_Usua, nest_Usua, cdir_Usua, "
                                             + " ccod_Pais, ccod_Dpto, ccod_Prov, "
                                             + " ccod_Dist, cmai_Usua, ctip_prio, "
                                             + " crol_Usua, cruc_usua, "
                                             + " ctel_Fijo, ctel_Celu ) "
                                            + " values (?,?,?, ?,?,?, ?,?,?, ?,?,?, "
                                            + " ?,?,?,?)");

                            statement.setString(i++, to.getCcod_Usua());
                            statement.setString(i++, to.getCnom_Usua());
                            statement.setString(i++, to.getCnic_Usua());
                            statement.setString(i++, to.getCcla_Usua());
                            statement.setInt(i++, to.getNest_Usua());
                            statement.setString(i++, to.getCdir_Usua());
                            statement.setString(i++, to.getCcod_Pais());
                            statement.setString(i++, to.getCcod_Dpto());
                            statement.setString(i++, to.getCcod_Prov());
                            statement.setString(i++, to.getCcod_Dist());
                            statement.setString(i++, to.getCmai_Usua());
                            statement.setString(i++, to.getCtip_Prio());
                            statement.setString(i++, to.getCrol_Usua());
                            statement.setString(i++, to.getCruc_Usua());
                            statement.setString(i++, to.getCtel_Fijo());
                            statement.setString(i++, to.getCtel_Celu());
                            int n = statement.executeUpdate();
                            statement.close();
                            if (n == 0)
                                    throw new TransactionException("No se pudo crear el usuario");
                        }
                        else {
                            Connection c;
                            PreparedStatement statement = null;
                            c = (Connection) transaction.getConnection();

                            statement = c.prepareStatement("update mysql.gemc_usua " +
                                      " set ccla_Usua = ?, " +
                                          " nest_Usua = ?, " +
                                          " cnom_Usua = ?, " +
                                          " cdir_Usua = ?, " +
                                          " cruc_Usua = ?, " +
                                          " cmai_Usua = ?, " +
                                          " ctip_Prio = ?, " +
                                          " crol_Usua = ?, " +
                                          " ctel_Fijo = ?, " +
                                          " ctel_Celu = ? " +
                                    " where ccod_usua = ?");
                            statement.setString(i++, to.getCcla_Usua());
                            statement.setInt(i++, to.getNest_Usua());
                            statement.setString(i++, to.getCnom_Usua());
                            statement.setString(i++, to.getCdir_Usua());
                            statement.setString(i++, to.getCruc_Usua());
                            statement.setString(i++, to.getCmai_Usua());
                            statement.setString(i++, to.getCtip_Prio());
                            statement.setString(i++, to.getCrol_Usua());
                            statement.setString(i++, to.getCtel_Fijo());
                            statement.setString(i++, to.getCtel_Celu());
                            //
                            statement.setString(i++, to.getCcod_Usua() );

                            int n = statement.executeUpdate();
                            statement.close();
                            if (n == 0)
                                    throw new TransactionException("No se pudo eliminar el usuario");
                        }
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
	}

	public void eliminar(String codUser ) throws TransactionException {
		try {
			int i = 1;
			Connection c;
			PreparedStatement statement = null;
			c = (Connection) transaction.getConnection();

			statement = c.prepareStatement("delete from mysql.gemc_usua where "
					+ " ccod_usua = ?");

			statement.setString(i++, codUser );			

			int n = statement.executeUpdate();
			statement.close();
			if (n == 0)
				throw new TransactionException("No se pudo eliminar el usuario");
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
	}

        public UsuarioTo buscarUsuario(String codUser ) throws TransactionException {
		Connection c;
		Statement statement = null;
		UsuarioTo usuario = null;
                String cruc_Usua;
                String cnom_Usua;
                String cnic_Usua;
                String ccla_Usua;
                int nest_Usua;
                String cdir_Usua;
                String ccod_Pais;
                String ccod_Dpto;
                String ccod_Prov;
                String ccod_Dist;
                String cmai_Usua;
                String ctip_Prio;
                String crol_Usua;
                String ctel_Fijo;
                String ctel_Celu;
                Date daud_Fec_Modi;
                String caud_Usu_Modi;
                Date daud_Fec_Crea;
                String caud_Usu_Crea;
                try {
			c = transaction.getConnection();
			statement = c.createStatement();
			ResultSet rs = statement.executeQuery("select "
					 + " ccod_Usua, cruc_Usua, cnom_Usua, "
                                         + " cnic_Usua, "
                                         + " ccla_Usua, nest_Usua,"
                                         + " cdir_Usua, ccod_Pais, "
                                         + " ccod_Dpto, ccod_Prov, ccod_Dist,"
                                         + " cmai_Usua, ctip_Prio, "
                                         + " crol_Usua, daud_Fec_Modi, caud_Usu_Modi, "
                                         + " daud_Fec_Crea, caud_Usu_Crea, "
                                         + " ctel_Fijo, ctel_Celu "
                                        + " From mysql.gemc_usua "
                                        + " Where ccod_usua = '" + codUser + "'" );
			while (rs.next()) {
                                cruc_Usua = rs.getString("cruc_Usua");
                                cnom_Usua = rs.getString("cnom_Usua");
                                cnic_Usua = rs.getString("cnic_Usua");
                                ccla_Usua = rs.getString("ccla_Usua");
                                nest_Usua = rs.getInt("nest_Usua");
                                cdir_Usua = rs.getString("cdir_Usua");
                                ccod_Pais = rs.getString("ccod_Pais");
                                ccod_Dpto = rs.getString("ccod_Dpto");
                                ccod_Prov = rs.getString("ccod_Prov");
                                ccod_Dist = rs.getString("ccod_Dist");
                                cmai_Usua = rs.getString("cmai_Usua");
                                ctip_Prio = rs.getString("ctip_Prio");
                                crol_Usua = rs.getString("crol_Usua");
                                daud_Fec_Modi = rs.getDate("daud_Fec_Modi");
                                caud_Usu_Modi = rs.getString("caud_Usu_Modi");
                                daud_Fec_Crea = rs.getDate("daud_Fec_Crea");
                                caud_Usu_Crea = rs.getString("caud_Usu_Crea");
                                ctel_Fijo = rs.getString("ctel_Fijo");
                                ctel_Celu = rs.getString("ctel_Celu");
                                usuario = new UsuarioTo( codUser, cruc_Usua, cnom_Usua,
                                                         cnic_Usua, ccla_Usua,
                                                         nest_Usua, 
                                                         cdir_Usua, ccod_Pais,
                                                         ccod_Dpto, ccod_Prov,
                                                         ccod_Dist, cmai_Usua,
                                                         ctip_Prio,
                                                         crol_Usua,
                                                         ctel_Fijo, ctel_Celu,
                                                         daud_Fec_Modi, caud_Usu_Modi,
                                                         daud_Fec_Crea, caud_Usu_Crea );
                        }
			rs.close();
			statement.close();
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
		return usuario;
	}
	
	public ArrayList arrayUsuarios() throws TransactionException {
		Connection c;
		Statement statement = null;
		ArrayList all = null;
		try {
			c = transaction.getConnection();
			statement = c.createStatement();
			ResultSet rs = statement.executeQuery("select * from mysql.gemc_usua");
			all = new ArrayList();
			while (rs.next()) {
                                String cruc_Usua = rs.getString("cruc_Usua");
                                String ccod_Usua = rs.getString("ccod_Usua");
                                String cnom_Usua = rs.getString("cnom_Usua");
                                String cnic_Usua = rs.getString("cnic_Usua");
                                String ccla_Usua = rs.getString("cnic_Usua");
                                int nest_Usua = rs.getInt("nest_Usua");
                                String cdir_Usua = rs.getString("cdir_Usua");
                                String ccod_Pais = rs.getString("ccod_Pais");
                                String ccod_Dpto = rs.getString("ccod_Dpto");
                                String ccod_Prov = rs.getString("ccod_Prov");
                                String ccod_Dist = rs.getString("ccod_Dist");
                                String cmai_Usua = rs.getString("cmai_Usua");
                                String ctip_Prio = rs.getString("ctip_Prio");
                                String crol_Usua = rs.getString("crol_Usua");
                                String ctel_Fijo = rs.getString("ctel_Fijo");
                                String ctel_Celu = rs.getString("ctel_Celu");
                                Date daud_Fec_Modi = rs.getDate("daud_Fec_Modi");
                                String caud_Usu_Modi = rs.getString("caud_Usu_Modi");
                                Date daud_Fec_Crea = rs.getDate("daud_Fec_Crea");
                                String caud_Usu_Crea = rs.getString("caud_Usu_Crea");
                                all.add(new UsuarioTo( ccod_Usua, cruc_Usua, cnom_Usua, cnic_Usua,
                                          ccla_Usua, nest_Usua,
                                          cdir_Usua, ccod_Pais,
                                          ccod_Dpto, ccod_Prov, ccod_Dist,
                                          cmai_Usua, ctip_Prio,
                                          crol_Usua, ctel_Fijo, ctel_Celu,
                                          daud_Fec_Modi, caud_Usu_Modi,
                                          daud_Fec_Crea, caud_Usu_Crea ));
			}
			rs.close();
			statement.close();
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
		return all;
	}

        public UsuarioTo buscarProveedor(String rucProv ) throws TransactionException {
		Connection c;
		Statement statement = null;
		UsuarioTo usuario = null;
                String ccod_Usua;
                String cnom_Usua;
                String cnic_Usua;
                String ccla_Usua;
                int nest_Usua;
                String cdir_Usua;
                String ccod_Pais;
                String ccod_Dpto;
                String ccod_Prov;
                String ccod_Dist;
                String cmai_Usua;
                String ctip_Prio;
                String crol_Usua;
                String ctel_Fijo;
                String ctel_Celu;
                Date daud_Fec_Modi;
                String caud_Usu_Modi;
                Date daud_Fec_Crea;
                String caud_Usu_Crea;
                try {
			c = transaction.getConnection();
			statement = c.createStatement();
			ResultSet rs = statement.executeQuery("select "
					 + " ccod_Usua, cnom_Usua, cnic_Usua,"
                                         + " ccla_Usua, nest_Usua, "
                                         + " cdir_Usua, ccod_Pais, "
                                         + " ccod_Dpto, ccod_Prov, ccod_Dist,"
                                         + " cmai_Usua, ctip_Prio, crol_Usua, "
                                         + " ctel_Fijo, ctel_Celu, "
                                         + " daud_Fec_Modi, caud_Usu_Modi, "
                                         + " daud_Fec_Crea, caud_Usu_Crea "
                                        + " From mysql.gemc_usua "
                                        + " Where cruc_Usua = '" + rucProv + "'" );
			while (rs.next()) {
                                ccod_Usua = rs.getString("ccod_Usua");
                                cnom_Usua = rs.getString("cnom_Usua");
                                cnic_Usua = rs.getString("cnic_Usua");
                                ccla_Usua = rs.getString("ccla_Usua");
                                nest_Usua = rs.getInt("nest_Usua");
                                cdir_Usua = rs.getString("cdir_Usua");
                                ccod_Pais = rs.getString("ccod_Pais");
                                ccod_Dpto = rs.getString("ccod_Dpto");
                                ccod_Prov = rs.getString("ccod_Prov");
                                ccod_Dist = rs.getString("ccod_Dist");
                                cmai_Usua = rs.getString("cmai_Usua");
                                ctip_Prio = rs.getString("ctip_Prio");
                                crol_Usua = rs.getString("crol_Usua");
                                ctel_Fijo = rs.getString("ctel_Fijo");
                                ctel_Celu = rs.getString("ctel_Celu");
                                daud_Fec_Modi = rs.getDate("daud_Fec_Modi");
                                caud_Usu_Modi = rs.getString("caud_Usu_Modi");
                                daud_Fec_Crea = rs.getDate("daud_Fec_Crea");
                                caud_Usu_Crea = rs.getString("caud_Usu_Crea");
                                usuario = new UsuarioTo( ccod_Usua, rucProv, cnom_Usua,
                                                         cnic_Usua, ccla_Usua,
                                                         nest_Usua, 
                                                         cdir_Usua, ccod_Pais,
                                                         ccod_Dpto, ccod_Prov,
                                                         ccod_Dist, cmai_Usua,
                                                         ctip_Prio,
                                                         crol_Usua,
                                                         ctel_Fijo, ctel_Celu,
                                                         daud_Fec_Modi, caud_Usu_Modi,
                                                         daud_Fec_Crea, caud_Usu_Crea);
                        }
			rs.close();
			statement.close();
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
		return usuario;
	}

}
